package org.study.clickhouse.plus.boot.util;

import com.alibaba.fastjson2.JSON;
import com.clickhouse.jdbc.ClickHouseDataSource;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.support.PropertiesLoaderUtils;
import org.springframework.util.CollectionUtils;
import org.study.clickhouse.plus.boot.model.User;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Date;
import java.sql.*;
import java.util.*;

/**
 * 传统 JDBC 方式操作 ClickHouse 数据库
 *
 * @author Administrator
 */
public class ClickHouseUtils {
    private static String url;
    private static String user;
    private static String password;

    static {
        System.out.println("读取 clickhouse.properties 属性文件开始……");
        ClassPathResource resource = new ClassPathResource("clickhouse.properties");
        try {
            Properties properties = PropertiesLoaderUtils.loadProperties(resource);
            Set<String> propertyNames = properties.stringPropertyNames();
            if (!CollectionUtils.isEmpty(propertyNames)) {
                System.out.println("==================================================");
                propertyNames.stream().forEach(item -> {
                    System.out.println(item + "=" + properties.getProperty(item));
                });
                System.out.println("==================================================");
            }
            url = properties.getProperty("jdbc.clickhouse.url");
            user = properties.getProperty("jdbc.clickhouse.user");
            password = properties.getProperty("jdbc.clickhouse.password");
        } catch (IOException e) {
            System.err.println("读取 clickhouse.properties 属性文件异常：" + e);
        }
        System.out.println("读取 clickhouse.properties 属性文件结束");
    }

    public static Connection getConnection() throws SQLException {
        try {
            Class.forName("com.clickhouse.jdbc.ClickHouseDriver");
        } catch (ClassNotFoundException e) {
            System.err.println("加载 com.clickhouse.jdbc.ClickHouseDriver 驱动失败！");
        }
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

    public static Connection getConnection2() throws SQLException {
        DataSource dataSource = new ClickHouseDataSource(url);
        Connection connection = dataSource.getConnection(user, password);
        return connection;
    }

    public static Connection getConnection3() throws SQLException {
        Properties props = new Properties();
        props.setProperty("user", user);
        props.setProperty("password", password);
        DataSource dataSource = new ClickHouseDataSource(url, props);
        return dataSource.getConnection();
    }

    /**
     * 参考 https://www.cnblogs.com/ljhbjehp/p/14659697.html
     *
     * @param sql
     * @return java.util.List<java.util.Map < java.lang.String, java.lang.Object>>
     */
    public static List<Map<String, Object>> executeSqlOld(String sql) {
        System.out.println("clickhouse 执行 sql：" + sql);
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            // 注意驱动包是 com.clickhouse.jdbc.ClickHouseDataSource，而不是 ru.yandex.clickhouse.ClickHouseDataSource(已过期)
            ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource(url);
            // 注意驱动包是 com.clickhouse.jdbc.ClickHouseConnection， 而不是 ru.yandex.clickhouse.ClickHouseConnection(已过期)
            try (Connection connection = DriverManager.getConnection(url, user, password);
                 Connection connection1 = clickHouseDataSource.getConnection(user, password);
                 Statement statement = connection.createStatement();
                 ResultSet resultSet = statement.executeQuery(sql)) {
                ResultSetMetaData metaData = resultSet.getMetaData();
                while (resultSet.next()) {
                    Map<String, Object> row = new HashMap<>();
                    for (int i = 1; i <= metaData.getColumnCount(); i++) {
                        row.put(metaData.getColumnName(i), resultSet.getObject(metaData.getColumnName(i)));
                    }
                    list.add(row);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 参考 https://www.cnblogs.com/ljhbjehp/p/14659697.html
     *
     * @param sql
     * @return java.util.List<java.util.Map < java.lang.String, java.lang.Object>>
     */
    public static List<Map<String, Object>> executeSql(String sql) {
        System.out.println("clickhouse 执行 sql：" + sql);
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            // 注意驱动包是 com.clickhouse.jdbc.ClickHouseDataSource，而不是 ru.yandex.clickhouse.ClickHouseDataSource(已过期)
            ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource(url);
            // 注意驱动包是 com.clickhouse.jdbc.ClickHouseConnection， 而不是 ru.yandex.clickhouse.ClickHouseConnection(已过期)
            try (Connection connection = DriverManager.getConnection(url, user, password);
                 Connection connection1 = clickHouseDataSource.getConnection(user, password);
                 PreparedStatement statement = connection.prepareStatement(sql);
                 ResultSet resultSet = statement.executeQuery()) {
                ResultSetMetaData metaData = resultSet.getMetaData();
                while (resultSet.next()) {
                    Map<String, Object> row = new HashMap<>();
                    for (int i = 1; i <= metaData.getColumnCount(); i++) {
                        row.put(metaData.getColumnName(i), resultSet.getObject(metaData.getColumnName(i)));
                    }
                    list.add(row);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static String insertBatch(List<User> list) {
        int[] batch = new int[0];
        String sql = "INSERT INTO t_user( id, username, password, phone, email, birthday, created_at, updated_at) VALUES ( ?, ?, ?, ?, ?, ? , ?, ?)";
        System.out.println("clickhouse 执行 sql：" + sql);
        try {
            // 注意驱动包是 com.clickhouse.jdbc.ClickHouseDataSource，而不是 ru.yandex.clickhouse.ClickHouseDataSource(已过期)
            ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource(url);
            // 注意驱动包是 com.clickhouse.jdbc.ClickHouseConnection， 而不是 ru.yandex.clickhouse.ClickHouseConnection(已过期)
            try (Connection connection = DriverManager.getConnection(url, user, password);
                 Connection connection1 = clickHouseDataSource.getConnection(user, password);
                 PreparedStatement pstmt = connection.prepareStatement(sql)) {
                for (int i = 0; i < list.size(); i++) {
                    User user = list.get(i);
                    pstmt.setLong(1, user.getId());
                    pstmt.setString(2, user.getUsername());
                    pstmt.setString(3, user.getPassword());
                    pstmt.setString(4, user.getPhone());
                    pstmt.setString(5, user.getEmail());
                    // 先赋默认值，后面给定了值在替换，保证不报 NPE 错误
                    long milliseconds = System.currentTimeMillis();
                    pstmt.setDate(6, new Date(milliseconds));
                    pstmt.setTimestamp(7, new Timestamp(milliseconds));
                    pstmt.setTimestamp(8, new Timestamp(milliseconds));
                    if (user.getBirthday() != null) {
                        pstmt.setDate(6, Date.valueOf(user.getBirthday()));
                    }
                    if (user.getCreatedAt() != null) {
                        pstmt.setTimestamp(7, Timestamp.valueOf(user.getCreatedAt()));
                    }
                    if (user.getUpdatedAt() != null) {
                        pstmt.setTimestamp(8, new Timestamp(user.getUpdatedAt().getTime()));
                    }
                    pstmt.addBatch();
                }
                batch = pstmt.executeBatch();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return JSON.toJSONString(batch);
    }
}
